<?php

class msales extends CI_Model {
    
    public function __construct()
    {
        parent::__construct();
        $this->load->library('serversidedatatables');
    }
    
    public function getPaymentOption() {
        //get all records from users table
        $query = $this->db->get('v_combo_payment_method');
        
        if( $query->num_rows() > 0 ) {
            return $query->result();
        } else {
            return array();
        }
    } //end getAll
    
    public function getCluster(){
		$dbconn  = $this->load->database("default", TRUE);
		$userlogin = $this->session->userdata('username');

		$sql = "SELECT f_get_territory_path(f_get_cluster('$userlogin')) as territory_name, f_get_cluster('$userlogin') as territory_id";		
		$query = $dbconn->query($sql);
        if( $query->num_rows() > 0) 
		{
            return $query->result();
        } 
		else {
            return array();
        }
       	// $this->db->select('territory_name, territory_id');
		// $this->db->from('t_mtr_territory');
		// $this->db->where('user_id', $this->session->userdata('username'));
        // $this->db->limit(1, 0);
		// $query = $this->db->get();

        // return $query->result();
        
    }

    
    public function salesOrderDetail($sales_id, $so_id) {
        $userlogin = $this->session->userdata('username');
        $dbconn = $this->load->database("default", TRUE);
        // $query  = $dbconn->query("SELECT d.iccid, d.price, d.discount, i.item_name, i.mdn, gr.item_group_name 
        //           FROM t_trx_sales_order_detail d 
        //           JOIN t_mtr_item i on d.iccid = i.iccid 
        //           JOIN t_mtr_item_group gr on i.item_group_id = gr.item_group_id  
        //           JOIN t_trx_sales_order s on s.so_id = d.so_id 
        //           WHERE d.so_id = '$so_id' AND s.sales_id = '$sales_id' ");
        
        $query  = $dbconn->query("SELECT d.iccid, d.price, d.discount, i.item_name, i.mdn, gr.item_group_name, 
                    si.channel_id, f_get_channel_name(si.channel_id) as partner, top.top_day
                    FROM t_trx_sales_order_detail d 
                    JOIN t_mtr_item i on d.iccid = i.iccid 
                    JOIN t_mtr_item_group gr on i.item_group_id = gr.item_group_id  
                    JOIN t_trx_sales_order s on s.so_id = d.so_id 
                    JOIN t_trx_sell_in_detail sd on sd.iccid = d.iccid
                    JOIN t_trx_sell_in si on sd.sell_in_id = si.sell_in_id
                    JOIN t_mtr_top top on si.top_id = top.top_id
                    WHERE d.so_id = '$so_id' AND s.sales_id = '$sales_id' ");
        return $query->result_array();
    }


    public function getById( $id ) {
        $id = intval( $id );
        
        $query = $this->db->where( 'so_id', $id )->limit( 1 )->get( 't_trx_sales_order' );
        
        if( $query->num_rows() > 0 ) {
            return $query->row();
        } else {
            return array();
        }
    }

    public function so_info($so_id) {
        $so_id = intval( $so_id );

        $this->db->select("t_trx_sales_order.*");
        $this->db->select("f_get_territory_path(t_trx_sales_order.territory_id) as territory", false);
        $this->db->select("t_mtr_user.user_name, t_mtr_user_group.user_group_caption");
        $this->db->join("t_mtr_user","t_trx_sales_order.sales_id = t_mtr_user.user_id");
        $this->db->join("t_mtr_user_group","t_mtr_user_group.user_group_id = t_mtr_user.user_group_id");
        $query = $this->db->get_where("t_trx_sales_order",array("so_id" => $so_id));
        return $query->row();

    }
    public function getSales($cluster_id)
    {
	
	    $dbconn  = $this->load->database("default", TRUE);
		$userlogin = $this->session->userdata('username');

        // yaddis@nutech-integrasi.com 1 agustus 2012
        // optimized on 09/28/2012
        $this->db->distinct();
        $this->db->select("sc.user_id");
        $this->db->join("t_trx_scan_in_detail AS sd","sd.scan_in_id = sc.scan_in_id");
	//anggwa 20121010
        //$this->db->join("t_trx_sell_in_detail AS sl","sl.iccid = sd.iccid");
        $this->db->where("sd.istatus", 2);
        $this->db->where("f_get_cluster(sc.user_id)", $cluster_id);
        $users = $this->db->get("t_trx_scan_in AS sc")->result();
        
        $user_id[]=null;
        foreach ($users as $key) 
            { $user_id[] = $key->user_id; }
            
        if (count($user_id)>0){   
            
        $this->db->select("uu.user_id, uu.user_name, gg.user_group_id, gg.user_group_caption");
        $this->db->join("t_mtr_user_group AS gg","gg.user_group_id = uu.user_group_id");
        $this->db->where_in("uu.user_id", $user_id );
        $this->db->order_by("gg.user_group_id, uu.user_name", "asc");
        $query = $this->db->get("t_mtr_user AS uu");
        
        if( $query->num_rows() > 0 ) {
            return $query->result();
          } else {
            return array();
           }
        }else{
          return array();  
        }
        // $this->db->select('user_id, user_name');
		// $this->db->from('t_mtr_user');
		// $this->db->join('t_mtr_user_group', 't_mtr_user.user_group_id = t_mtr_user_group.user_group_id AND t_mtr_user.istatus = 1 AND t_mtr_user_group.having_stock = 1', 'left');
	    // $this->db->order_by("t_mtr_user.user_name", "asc");
		//$query = $this->db->get();
        //return $query->result();
    }

    //edit by alfin akhret
    function getSalesCurrentUser()
    {
        $dbconn  = $this->load->database("default", TRUE);
        $userlogin = $this->session->userdata('username');
        $query = $dbconn->query("SELECT DISTINCT usr.user_id, usr.user_name, f_get_cluster(usr.user_id) as clusterId
                FROM t_mtr_user usr, 
                t_trx_scan_in scn, t_trx_scan_in_detail d
                WHERE scn.user_id = usr.user_id 
                and scn.scan_in_id = d.scan_in_id
                and d.istatus = 2
                and usr.user_id = '$userlogin'
                ORDER BY usr.user_name ASC");
        if( $query->num_rows() > 0 ) {
            return $query->result();
          } else {
            return array();
           }
    }
    
    public function getSalesOrder($sales_id)
    {
		$userlogin = $this->session->userdata('username');

        $this->db->distinct();
        $this->db->select("d.iccid, h.channel_id, f_get_channel_name(h.channel_id) AS partner, itm.item_name, itm.mdn, grp.item_group_name", FALSE);
        $this->db->select("f_get_price(d.iccid, h.user_id) AS reseller_price, f_get_price(d.iccid, '$sales_id') as default_price", FALSE);
        $this->db->select("h.sell_in_date, top.top_day");
        $this->db->from("t_trx_sell_in_detail as d");
        $this->db->join("t_trx_sell_in as h","h.sell_in_id = d.sell_in_id");
        $this->db->join("t_mtr_top as top","h.top_id = top.top_id");
        $this->db->join("t_mtr_item as itm","itm.iccid = d.iccid");
        $this->db->join("t_mtr_item_group as grp","itm.item_group_id = grp.item_group_id");
        $this->db->join("t_trx_scan_in_detail as sd","sd.iccid = d.iccid");
        $this->db->join("t_trx_scan_in as sci","sci.scan_in_id = sd.scan_in_id");
        // $this->db->where("d.istatus", 1);
        $this->db->where_in("d.istatus", array(1,4));  // 1 => sell in valid, 4 => sell out
        // $this->db->where("h.user_id", $sales_id);
        $this->db->where("sci.user_id", $sales_id);
        $query = $this->db->get();

		if( $query->num_rows() > 0 ) {
			return $query->result_array();
            $query->free_result();
        } 
		else {
            $query->free_result();
            return array();
        }

        
    }
    
    public function getPaymentMethod()
    {
        $this->db->select('*')->from('v_combo_payment_method');
        $query = $this->db->get();   
 
        return $query->result();   
    }
    
	//populate barang yang harus di bayar
    public function get_iccid()
    {
        // $this->db->select('t_trx_scan_in_detail.iccid,
                            // t_mtr_item_group.item_group_name,
                            // t_mtr_item.item_name,
                            // f_get_price(t_trx_scan_in_detail.iccid, t_trx_scan_in.user_id),
                            // f_get_price(t_trx_scan_in_detail.iccid, t_trx_scan_in.user_id)');
		// $this->db->from('t_trx_scan_in_detail');
		// $this->db->join('t_trx_scan_in', 't_trx_scan_in.scan_in_id = t_trx_scan_in_detail.scan_in_id');
		// $this->db->join('t_mtr_item', 't_trx_scan_in_detail.iccid = t_mtr_item.iccid AND t_mtr_item.istatus = 2 AND t_trx_scan_in_detail.istatus=2');
        // $this->db->join('t_mtr_item_mapping', 't_mtr_item.item_code = t_mtr_item_mapping.item_code');
        // $this->db->join('t_mtr_item_group', 't_mtr_item_mapping.item_group_id = t_mtr_item_group.item_group_id');
		// $query = $this->db->get();		
        // return $query->result_array();

		/**anggwa@gmail.com 20120508 --optimization*/				
		$dbconn = $this->load->database("default", TRUE);
		// $sql = "SELECT d.iccid, grp.item_group_name
				// , itm.item_name
				// , f_get_price(d.iccid, h.user_id) as reseller_price
				// , f_get_price(d.iccid, h.user_id) as default_price
				// FROM t_trx_scan_in_detail d
				// JOIN t_trx_scan_in h ON h.scan_in_id = d.scan_in_id 
				// JOIN t_trx_sell_in_detail x ON x.iccid = d.iccid AND x.istatus = 1
				// JOIN t_mtr_item itm ON d.iccid = itm.iccid 
				// AND itm.istatus = 2 AND d.istatus=2 
				// JOIN t_mtr_item_mapping imp ON itm.item_code = imp.item_code 
				// JOIN t_mtr_item_group grp ON imp.item_group_id = grp.item_group_id";
		$query = $dbconn->query("SELECT d.iccid, grp.item_group_name
				, itm.item_name
				, f_get_price(d.iccid, h.user_id) as reseller_price
				, f_get_price(d.iccid, h.user_id) as default_price
				FROM t_trx_scan_in_detail d
				JOIN t_trx_scan_in h ON h.scan_in_id = d.scan_in_id 
				JOIN t_trx_sell_in_detail x ON x.iccid = d.iccid AND x.istatus = 1
				JOIN t_mtr_item itm ON d.iccid = itm.iccid 
				AND itm.istatus = 2 AND d.istatus=2 
				JOIN t_mtr_item_mapping imp ON itm.item_code = imp.item_code 
				JOIN t_mtr_item_group grp ON imp.item_group_id = grp.item_group_id");
		
		if( $query->num_rows() > 0 ) {
			return $query->result_array();
        } 
		else {
            return array();
        }
		/**anggwa@gmail.com 20120508 --optimization*/				
		
		
    }
    
    public function get_single_iccid($iccid, $userid)
    {
        //$this->db->select("f_get_price('$iccid','$userID'), item_group_name, item_name");
		//$this->db->from('t_mtr_item');
        //$this->db->join('t_mtr_item_group', 't_mtr_item.item_group_id = t_mtr_item_group.item_group_id');
        //$this->db->where("iccid = '$iccid'");
		//$query = $this->db->get();
        //return $query->row_array();

		/**anggwa@gmail.com 20120508 --optimization*/				
        $dbconn = $this->load->database("default", TRUE);
        // $sql = "SELECT f_get_price('$iccid','$userid'),itm.item_name,grp.item_group_name 
                 // from t_mtr_item itm, t_mtr_item_mapping imp, t_mtr_item_group grp
                 // where iccid = '$iccid' 
				 // AND imp.item_code = itm.item_code
				 // AND imp.item_group_id = grp.item_group_id";
     //    $query = $dbconn->query("SELECT f_get_price('$iccid','$userid'),itm.item_name,grp.item_group_name 
     //             from t_mtr_item itm, t_mtr_item_mapping imp, t_mtr_item_group grp
     //             where iccid = '$iccid' 
				 // AND imp.item_code = itm.item_code
				 // AND imp.item_group_id = grp.item_group_id LIMIT 1");


        $query = $dbconn->query("SELECT f_get_channel_name(sellh.channel_id) as partner, h.scan_in_id,
                           i.item_code, i.mdn, f_get_item_group(d.iccid) as item_group_name, i.item_name, 
                           f_get_price(d.iccid, '$userid') as reseller_price, f_get_price_retail(d.iccid) as default_price 
                           FROM t_trx_scan_in_detail d 
                           JOIN t_trx_scan_in h ON d.scan_in_id = h.scan_in_id
                           JOIN t_trx_sell_in_detail selld ON d.iccid = selld.iccid 
                           JOIN t_trx_sell_in sellh ON selld.sell_in_id = sellh.sell_in_id 
                           JOIN t_mtr_item i ON d.iccid = i.iccid  
                           AND i.istatus = 2
                           WHERE d.iccid = '$iccid'");
       return $query->row_array();

		// if( $query->num_rows() > 0 ) {
		// 	$res = $query->row_array();
		// 	return $res;
  //       } 
		// else {
  //           return array();
  //       }
		/**anggwa@gmail.com 20120508 --optimization*/				
    }
    
    public function get_single_iccid_2($iccid)
    {
        $this->db->select("f_get_price('$iccid','$userID'), f_get_item_group('$iccid') as item_group_name, item_name");
		$this->db->from('t_mtr_item');
        $this->db->where("iccid = '$iccid'");
		$query = $this->db->get();
        return $query->row_array();
        /**$dbconn = $this->load->database("default", TRUE);
        $sql = "SELECT f_get_price('$iccid','$userid'),item_name,item_group_name 
                 from t_mtr_item, t_mtr_item_group 
                 where iccid = '$iccid' AND t_mtr_item.item_group_id = t_mtr_item_group.item_group_id";
        $query = $dbconn->query($sql);
		
		if( $query->num_rows() > 0 ) {
			$res = $query->row_array();
			return $res;
        } 
		else {
            return array();
        }*/
    }
    
    public function generateReport($salesId, $Id)
    {
        $dbconn = $this->load->database("default", TRUE);
        // $sql = "SELECT imap.item_code AS kode_barang, i.item_name AS deskripsi, COUNT(imap.item_code) AS jumlah, 
                // f_get_price(imap.item_code, '$salesId'), (d.price * d.discount/100) as diskon,  d.price as harga
                // FROM t_trx_sales_order_detail d
                // JOIN t_trx_sales_order h on d.so_id = h.so_id
                // JOIN t_mtr_item i on d.iccid = i.iccid
                // JOIN t_mtr_item_mapping imap on imap.item_code = i.item_code
                // where h.so_id = $Id
                // GROUP BY h.so_id, imap.item_code, i.item_name, d.discount, d.price
                // ORDER BY i.item_name ASC";
                
        // $query = $dbconn->query("SELECT d.iccid as iccid, i.mdn AS mdn, imap.item_code AS kode_barang, i.item_name AS deskripsi, 
        $query = $dbconn->query("SELECT i.mdn AS mdn, imap.item_code AS kode_barang, i.item_name AS deskripsi, count(imap.item_code) AS jumlah,
                f_get_price(imap.item_code, '$salesId'), (d.price * d.discount/100) as diskon,  d.price as harga
                FROM t_trx_sales_order_detail d
                JOIN t_trx_sales_order h on d.so_id = h.so_id
                JOIN t_mtr_item i on d.iccid = i.iccid
                JOIN t_mtr_item_mapping imap on imap.item_code = i.item_code
                where h.so_id = $Id
                GROUP BY i.mdn, h.so_id, imap.item_code, i.item_name, d.discount, d.price
                ORDER BY i.item_name ASC");
		
		if( $query->num_rows() > 0 ) {
			$res = $query->result_array();
			return $res;
        } 
		else {
            return array();
        }

    }
    
    
    function saveSalesOrder()
    {
        $dblokal = $this->load->database("default", true);
        $territory_id = $this->input->post("territory_id");
        $so_date = $this->input->post("so_date");
        $sales_id = $this->input->post("sales_id");
        $discount_total = $this->input->post("discount_total");
        $cash_paid = $this->input->post("total");
        $payment_method = $this->input->post("dListPaymentMethod");
        $remark = $this->input->post("remark");

        $data = array("territory_id" => $territory_id, "so_date" => $so_date, "sales_id" => $sales_id,
            "discount" => $discount_total, "cash_paid" => $cash_paid, "payment_method" => $payment_method, "created_by" => $this->session->userdata('username'), "updated_by" => $this->session->userdata('username'), "remark" => $remark);

        $dblokal->insert("t_trx_sales_order", $data);

		// 20120514 anggwa@gmail.com -> log
		$lastId = $this->db->insert_id();
		$this->mglobal->saveLog('sales-order','create',$lastId);		

        $freshSalesOrder = $this->getFreshSalesOrder();
        return $freshSalesOrder;
        
    }

    function saveSalesOrderDetail($so_id, $i)
    {
        $dblokal = $this->load->database("default", true);
        $iccid = $this->input->post('iccid');
        // $item_code = $this->input->post('item_code');
        // $item_group_name = $this->input->post('item_group_name');
        // $item_name = $this->input->post('item_name');
        $price = $this->input->post('default_price');
        $discount = $this->input->post('reseller_disc');
        // $scan_in_id = $this->input->post('scan_in_id');

        $data = array(
            "iccid" => $iccid[$i], 
            "so_id" => $so_id, 
            "price" => $price[$i], 
            "discount" => $discount[$i], 
            "created_by" => $this->session->userdata('username'), 
            "updated_by" => $this->session->userdata('username')
            );

        $dblokal->insert("t_trx_sales_order_detail", $data);


            $data2 = array(
                "istatus" => 3, 
                "iccid" => $iccid[$i]."#so#".$so_id,
                "transaction_type" => 'Sales Order',
                "transaction_id" => $so_id
                );
            // $this->db->where(array("scan_in_id" => $scan_in_id));
            $this->db->where("iccid",$iccid[$i]);
            $this->db->update("t_trx_scan_in_detail", $data2);

            $this->db->where("iccid",$iccid[$i]);
            $this->db->update("t_trx_sell_in_detail", array("istatus" => 2, "updated_by" => $this->session->userdata('username')));

		// 20120514 anggwa@gmail.com -> log
		$lastId = $this->db->insert_id();
		$this->mglobal->saveLog('sales-order-detail','create','(header='.$so_id.' detail='.$lastId.' iccid='.$iccid[$i].')');

    }
    
    function save_sales_order($sales_id) {
        $dblokal = $this->load->database("default", true);
        $territory_id = $this->input->post("territory_id");
        $so_date = $this->input->post("so_date");
        $sales_id = $this->input->post("sales_id");
        $discount_total = $this->input->post("discount_total");
        $cash_paid = $this->input->post("total");
        $payment_method = $this->input->post("dListPaymentMethod");
        $remark = $this->input->post("remark");

        $data = array(
            "territory_id" => $territory_id, 
            "so_date" => $so_date, 
            "sales_id" => $sales_id,
            "discount" => $discount_total, 
            "cash_paid" => $cash_paid, 
            "payment_method" => $payment_method, 
            "created_by" => $this->session->userdata('username'), 
            "updated_by" => $this->session->userdata('username'),
            "remark" => $remark);

        $dblokal->insert("t_trx_sales_order", $data);

        $so_id = $this->db->insert_id();
        $this->mglobal->saveLog('sales-order','create',$so_id);        

        return $so_id;
    }

    function save_so_detail($so_id, $iccid) {

    }

    function validate_item($iccid, $sales_id) {
        $this->db->select("t_trx_sales_order_detail.iccid");
        $this->db->join("t_trx_sales_order","t_trx_sales_order.so_id = t_trx_sales_order_detail.so_id");
        $this->db->where("t_trx_sales_order.sales_id", $sales_id);
        $this->db->where("t_trx_sales_order_detail.iccid", $iccid);
        $query = $this->db->get('t_trx_sales_order_detail', 1);

        if ($query->row()) {
            return false;
        }
        else {
            return true;
        }

    }


    function getFreshSalesOrder($cluster_id)
    {
	$userlogin = $this->session->userdata('username');
	$dbconn  = $this->load->database("default", TRUE);
    $current_date = date("Y-m-d");
	$sql = "SELECT * FROM t_trx_sales_order WHERE territory_id = $cluster_id AND so_date = '$current_date' ORDER BY so_id DESC";
	$query = $dbconn->query($sql);
		
	if( $query->num_rows() > 0 ) {
		return $query->row();
        } 
		else {
            return array();
        }

        //$this->db->select("*")->from("t_trx_sales_order");
        //$this->db->where("territory_id","f_get_cluster('$userlogin')");
        //$this->db->order_by("so_id", "desc");
        //$query = $this->db->get();   
 
        //return $query->row();    
        
    }
    
    function lookup($keyword){
        $this->db->select('*')->from('t_mtr_user');
        $this->db->like('user_name',$keyword,'after');
        $query = $this->db->get();   
 
        return $query->result();
    }
    
    public function getAll($cluster_id, $type = '') {
        //get all records from users table
        /**$query = $this->db->get( 't_trx_sales_order' );
        
        if( $query->num_rows() > 0 ) {
            return $query->result();
        } else {
            return array();
       }*/
        $userlogin = $this->session->userdata('username');
        $dbconn = $this->load->database("default", TRUE);

                                
        //$sqlCluster = "select f_get_cluster('$username')";
        //$queryCluster = $this->db->query($sqlCluster)->row()->f_get_cluster;

        //$sqlTerritory = "select f_get_territory_path_v2($queryCluster)";
    $current_date = date("Y-m-d");

    /** SSP
    /* @author alfin akhret <alfin.akhret@gmail.com>
    /* @date 01/10/2012
    */

    $sIndexColumn = "so_id"; 
    $status="";
    $defaultOrder = "0";
    $orderingSequence = "desc";
    $flag = '';
    $aColumns = array("so_id", "so_date", "user_name", "cash_paid");

    if($type == 'history')
    {
        $inputQuery = "
                    SELECT ".str_replace(" , ", " ", implode(", ", $aColumns))."
                    from v_ssp_sales_order where territory_id = $cluster_id and so_date BETWEEN CURRENT_DATE - integer '8' AND CURRENT_DATE - integer '1'";
    }
    else
    {
        $inputQuery = "
                    SELECT ".str_replace(" , ", " ", implode(", ", $aColumns))."
                    from v_ssp_sales_order where territory_id = $cluster_id and so_date = '$current_date'";
    }

    return $this->serversidedatatables->executeServerSideProcess($defaultOrder, $flag, $userlogin, $inputQuery, $aColumns, $sIndexColumn, $orderingSequence);

	// $sql = "SELECT *, f_get_territory_path($cluster_id) 
	// 	 FROM t_trx_sales_order h
	// 	 JOIN t_mtr_user u ON h.sales_id = u.user_id
	// 	 WHERE h.territory_id = $cluster_id
 //         AND h.so_date = '$current_date'
	// 	 ORDER BY h.so_id DESC";
	
                                
	// $dbconn = $this->load->database("default", TRUE);
	// $query = $dbconn->query($sql);
	// if( $query->num_rows() > 0 ) {
	// 	return $query->result();
 //        }
 // 	else {
 //            return array();
 //        }
                                
        //$this->db->select("*, f_get_territory_path_v2($queryCluster)");
	//	$this->db->from('t_trx_sales_order');
	//	$this->db->join('t_mtr_user', 't_trx_sales_order.sales_id = t_mtr_user.user_id');
       // $this->db->join('t_mtr_territory', 't_trx_sales_order.territory_id = t_mtr_territory.territory_id');
	//	$query = $this->db->get();
       // return $query->result();                
    } //end getAll
    
    //edited by Alfin Akhret
    function getAllCurrentUser($cluster_id, $type = null)
    {
        // $userlogin = $this->session->userdata('username');
        
        // $current_date = date("Y-m-d");

        // $sql = "SELECT *, f_get_territory_path($cluster_id) 
        //  FROM t_trx_sales_order h
        //  JOIN t_mtr_user u ON h.sales_id = u.user_id
        //  WHERE u.user_id = '$userlogin'
        //  AND h.so_date = '$current_date'
        //  ORDER BY h.so_id DESC";

        //  $dbconn = $this->load->database("default", TRUE);
        // $query = $dbconn->query($sql);
        // if( $query->num_rows() > 0 ) {
        //     return $query->result();
        //     } 
        // else {
        //         return array();
        //     }

        $userlogin = $this->session->userdata('username');
        $dbconn = $this->load->database("default", TRUE);

        $current_date = date("Y-m-d");
        $sIndexColumn = "so_id"; 
        $status="";
        $defaultOrder = "0";
        $orderingSequence = "desc";
        $flag = '';
        $aColumns = array("so_id", "so_date", "user_name", "cash_paid");

        if($type == 'history')
        {
            $inputQuery = "
                        SELECT ".str_replace(" , ", " ", implode(", ", $aColumns))."
                        from v_ssp_sales_order_smile where so_date BETWEEN CURRENT_DATE - integer '8' AND CURRENT_DATE - integer '1' and user_id = '".$userlogin."'";
        }
        else
        {
            $inputQuery = "
                        SELECT ".str_replace(" , ", " ", implode(", ", $aColumns))."
                        from v_ssp_sales_order_smile where so_date = '$current_date' and user_id = '".$userlogin."'";
        }
    
        return $this->serversidedatatables->executeServerSideProcess($defaultOrder, $flag, $userlogin, $inputQuery, $aColumns, $sIndexColumn, $orderingSequence);
    }

    function lookup_iccid($keyword)
    {
        $this->db->select('*')->from('t_mtr_item');
        $this->db->where('istatus', 2);
        $this->db->like('iccid',$keyword,'after');
        
        $query = $this->db->get();   
 
        return $query->result();    
    }
    
    
    /* ########################################################################################################## */
    /* ################################     TAMBAH FUNGSI 20 Maret 2012     ##################################### */
    /* ################################     Edited By Rizky Ramadhan        ##################################### */
    /* ########################################################################################################## */
     public function getSalesByOrder( $id ) {
        
        $query = $this->db->where( 'user_id', $id )->limit( 1 )->get( 't_mtr_user' );
        
        if( $query->num_rows() > 0 ) {
            return $query->row_array();
        } else {
            return array();
        }
    }
    
    public function getDistributor(){
        //get all records from users table
        //$this->db->order_by("transaction.id", "desc"); 
       	$this->db->select('territory_name, territory_id');
		$this->db->from('t_mtr_territory');
		$this->db->where('user_id', $this->session->userdata('username'));
        // $this->db->limit(1);
		$query = $this->db->get();

        return $query->row_array();
        
    }
    
    public function getca($username)
    {
        $this->db->select('reporting_to');
		$this->db->from('t_mtr_user');
		$this->db->where('user_id', $username);
        $this->db->limit(1, 0);
		$query = $this->db->get(); 
        return $query->row_array();   
    }
    
    public function getUsername($username)
    {
        $this->db->select('user_name');
		$this->db->from('t_mtr_user');
		$this->db->where('user_id', $username);
        $this->db->limit(1, 0);
		$query = $this->db->get(); 
        return $query->row_array();   
    }
    
    public function getSODetail($so_id)
    {
        $query = $this->db->where( 'so_id', $so_id )->get( 't_trx_sales_order_detail' );
        
        if( $query->num_rows() > 0 ) {
            return $query->result_array();
        } else {
            return array();
        }    
    }
    
    /**########################################################################################################## */
    /**################################     END     ############################################################# */
    /**########################################################################################################## */
    
    
    
    /**########################################################################################################### */
    /**################################     TAMBAH FUNGSI 24 Maret 2012     ##################################### */
    /**################################     Edited By Rizky Ramadhan     ##################################### */
    /**########################################################################################################### */
     
    public function updtScan()
    {
        $scan_in_id = $this->input->post('scan_in_id');
        $iccid = $this->input->post('iccid');
		//$data = array("istatus" => 3);
        
        //$this->db->trans_begin();
        
        for($i=0; $i<sizeof($iccid); $i++)
        {
			$data = array("istatus" => 3, "iccid" => $iccid[$i]."#so#".$scan_in_id);
            $this->db->where(array("scan_in_id" => $scan_in_id));
            //$this->db->where("scan_in_id", $scan_in_id);
            $this->db->where("iccid",$iccid[$i]);
            $this->db->update("t_trx_scan_in_detail", $data);
        }
        
        for($i=0; $i<sizeof($iccid); $i++)
        {
			//$data = array("istatus" => 3, "iccid" => $iccid[$i]."#so#".$scan_in_id);
			$data = array("istatus" => 2);
            //$data = array("istatus" => 2);
            //$this->db->where(array("iccid" => $iccid[$i]));
            $this->db->where("iccid",$iccid[$i]);
            $this->db->update("t_trx_sell_in_detail", $data);
        }

        //$this->db->where(array("scan_in_id" => $scan_in_id));
        //$this->db->update("t_trx_scan_in", $data); 
        //$this->db->update("t_trx_scan_in_detail", $data); 
        
        // if ($this->db->trans_status() === FALSE)
        // {
            // $this->db->trans_rollback();
        // }
        // else
        // {
            // $this->db->trans_commit();
        // }  
    }
    
    public function insScan()
    {
        
    }
    
    public function updtSell()
    {
        $iccid = $this->input->post('iccid');
        
        //$this->db->trans_begin();        
        for($i=0; $i < sizeof($iccid); $i++)
        {
            $single_iccid = $iccid[$i];
            $data = array("istatus" => 2);
            $this->db->where("iccid", $single_iccid);
            //$this->db->where(array("iccid" => $single_iccid));
            $this->db->update("t_trx_sell_in_detail", $data);    
        }
        
        // if ($this->db->trans_status() === FALSE)
        // {
            // $this->db->trans_rollback();
        // }
        // else
        // {
            // $this->db->trans_commit();
        // }                 
                    
    }
      
  ## yaddis@nutech --- 09/09/2012
  public function validate_iccid($iccid)
  {
    // $this->db->select("iccid");
    $query = $this->db->get_where("t_trx_sales_order_detail",array("iccid" => $iccid));
    if ($query->num_rows() > 0)
        { return $query->row()->iccid; }
    else 
        { return false ;}

  }
    
  public function create_so($data)
  {
    $this->db->insert('t_trx_sales_order', $data);
    $so_id = $this->db->insert_id();
    $this->mglobal->saveLog('sales-order','create',$so_id);        
    return $so_id;
  }

  public function so_detail($data)
  {
    $this->db->insert('t_trx_sales_order_detail', $data);
    $sod_id = $this->db->insert_id();

    $this->_settlement($data["so_id"], $data["iccid"]);
    $this->mglobal->saveLog('sales-order-detail','create','{detail='.$sod_id.' ,header='.$data["so_id"].', iccid='.$data["iccid"].'}');        
    return $sod_id;
  }

  private function _settlement($so_id, $iccid)
  {

    $data2 = array(
        "istatus" => 3, 
        "iccid" => $iccid."#so#".$so_id,
        "transaction_type" => 'Sales Order',
        "transaction_id" => $so_id
        );
    $this->db->where("iccid",$iccid);
    $this->db->update("t_trx_scan_in_detail", $data2 );

    $this->db->where("iccid",$iccid);
    $this->db->update("t_trx_sell_in_detail", array("istatus" => 2, "updated_by" => $this->session->userdata('username'), "updated_on" => date('Y-m-d H:i:s') ) );
    // $this->mglobal->saveLog('sales-order-settlement','create','{detail='.$so_id.' , iccid='.$iccid.'}');        
  }
    /**########################################################################################################### */
    /**################################     END     ############################################################### */
    /**############################################################################################################ */
    /**########################################################################################################### */
} //end class